package com.yang.utils;
import java.io.FileInputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Properties;

import com.alibaba.fastjson.JSON;
import com.mysql.jdbc.PreparedStatement;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

public class MySqlCreateInsertSql {


    public static Connection conn = null;
    public static PreparedStatement pst = null;

    public static void dbHelper(TableConfigVo tableConfigVo) {
        try {
            Class.forName("com.mysql.jdbc.Driver");//指定连接类型
            conn = DriverManager.getConnection(tableConfigVo.getConnectionUrl(), tableConfigVo.getConnectionUser(), tableConfigVo.getConnectionPwd());//获取连接
            //pst = conn.prepareStatement(sql);//准备执行语句
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void dbHelperCloseResource() throws SQLException {
        if(Objects.nonNull(conn)){
            conn.close();
        }
        if(Objects.nonNull(pst)){
            pst.close();
        }
    }
    public static void main(String[] args) throws SQLException {
        try {
            Properties prop = new Properties();
            URL url = CreatJpaFeildsUtils.class.getClassLoader().getResource("config.properties");
            prop.load(new FileInputStream(url.getPath()));
            TableConfigVo tableConfigVo = getTableConfigVo(prop);
            checkParams(tableConfigVo);
            dbHelper(tableConfigVo);
            Map<String, String> tableHeader = getTableHeader(getHeaderSql(tableConfigVo));
            Map<String, String> tableHeaders = getTableHeader(tableConfigVo.getQuerySql());

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbHelperCloseResource();
        }

    }

    private static Map<String, String> getTableHeader(String sql) {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            ResultSet resultSet = conn.prepareStatement(sql).executeQuery();
            ResultSetMetaData md = resultSet.getMetaData();
            int columnCount = md.getColumnCount();   //获得列数

            while (resultSet.next()) {
                Map<String, Object> data = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = md.getColumnName(i);
                    if(Objects.equals(columnName, "variable")){
                        continue;
                    }
                    data.put(columnName, resultSet.getObject(i));
                }
                System.out.println(data);
                System.out.println();
                list.add(data);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return null;
    }

    private static String getHeaderSql(TableConfigVo tableConfigVo) {
        return "SELECT column_name FROM information_schema.columns WHERE table_schema='"
                         +tableConfigVo.getTableSchema()+"' AND table_name='"+tableConfigVo.getTable()+"'";
    }


    private static TableConfigVo getTableConfigVo(Properties prop) {
        TableConfigVo tableConfigVo = new TableConfigVo();
        tableConfigVo.setConnectionUrl(prop.getProperty("connection.url"));
        tableConfigVo.setConnectionUser(prop.getProperty("connection.username"));
        tableConfigVo.setConnectionPwd(prop.getProperty("connection.password"));
        tableConfigVo.setTable(prop.getProperty("table.name"));
        String resetColumnValues = prop.getProperty("reset.column.value");
        if(!StringUtils.isEmpty(resetColumnValues)){
            Map resetColumnValuesMap = JSON.parseObject(resetColumnValues, Map.class);
            tableConfigVo.setResetColumnValues(resetColumnValuesMap);
        }
        String excludeColumns = prop.getProperty("exclude.column");
        if(!StringUtils.isEmpty(excludeColumns)){
            String[] split = excludeColumns.split(",");
            tableConfigVo.setExcludeColumn(Arrays.asList(split));
        }
        tableConfigVo.setOutputPat(prop.getProperty("output.path"));
        tableConfigVo.setQuerySql(prop.getProperty("query.sql"));
        tableConfigVo.setTableSchema(prop.getProperty("table.schema"));
        System.out.println(tableConfigVo);
        return tableConfigVo;
    }

    private static void checkParams(TableConfigVo tableConfigVo) {
        Assert.isTrue(!StringUtils.isEmpty(tableConfigVo.getConnectionUrl()), "Database connection url is required.");
        Assert.isTrue(!StringUtils.isEmpty(tableConfigVo.getConnectionUser()), "Database connection user is required.");
        Assert.isTrue(!StringUtils.isEmpty(tableConfigVo.getConnectionPwd()), "Database connection password is required.");
        Assert.isTrue(!StringUtils.isEmpty(tableConfigVo.getOutputPat()), "Sql output path is required.");
        Assert.isTrue(!StringUtils.isEmpty(tableConfigVo.getQuerySql()), "Query sql is required.");
        Assert.isTrue(!StringUtils.isEmpty(tableConfigVo.getTableSchema()), "Table schema is required.");
        Assert.isTrue(StringUtils.startsWithIgnoreCase(tableConfigVo.getQuerySql(),"select"), "Query sql must be start with select");
    }


}
class TableConfigVo{
    private String connectionUrl;
    private String connectionUser;
    private String connectionPwd;
    private String table;
    private List<String> excludeColumn;
    private Map<String, String> resetColumnValues;
    private String outputPat;
    private String querySql;
    private String tableSchema;

    public String getConnectionUrl() {
        return connectionUrl;
    }

    public void setConnectionUrl(String connectionUrl) {
        this.connectionUrl = connectionUrl;
    }

    public String getConnectionUser() {
        return connectionUser;
    }

    public void setConnectionUser(String connectionUser) {
        this.connectionUser = connectionUser;
    }

    public String getConnectionPwd() {
        return connectionPwd;
    }

    public void setConnectionPwd(String connectionPwd) {
        this.connectionPwd = connectionPwd;
    }

    public String getTable() {
        return table;
    }

    public void setTable(String table) {
        this.table = table;
    }

    public List<String> getExcludeColumn() {
        return excludeColumn;
    }

    public void setExcludeColumn(List<String> excludeColumn) {
        this.excludeColumn = excludeColumn;
    }

    public Map<String, String> getResetColumnValues() {
        return resetColumnValues;
    }

    public void setResetColumnValues(Map<String, String> resetColumnValues) {
        this.resetColumnValues = resetColumnValues;
    }

    public String getOutputPat() {
        return outputPat;
    }

    public void setOutputPat(String outputPat) {
        this.outputPat = outputPat;
    }

    public String getQuerySql() {
        return querySql;
    }

    public void setQuerySql(String querySql) {
        this.querySql = querySql;
    }

    public String getTableSchema() {
        return tableSchema;
    }

    public void setTableSchema(String tableSchema) {
        this.tableSchema = tableSchema;
    }

    @Override
    public String toString() {
        return "TableConfigVo{" +
               "connectionUrl='" + connectionUrl + '\'' +"\n"+
               ", connectionUser='" + connectionUser + '\'' +"\n"+
               ", connectionPwd='" + connectionPwd + '\'' +"\n"+
               ", table='" + table + '\'' +"\n"+
               ", excludeColumn=" + excludeColumn +"\n"+
               ", resetColumnValues=" + resetColumnValues +"\n"+
               ", outputPat='" + outputPat + '\'' +"\n"+
               ", querySql='" + querySql + '\'' +"\n"+
               ", tableSchema='" + tableSchema + '\'' +"\n"+
               '}';
    }
}